﻿using System.Data;
using HRMSystemShi.Model;
using Microsoft.Data.SqlClient;

namespace HRMSystemShi.DAL
{
    public class OperationLogService
    {
        string connstr = "Server=(localdb)\\MSSQLLocalDB; Database=HRMDB; User Id = hrmtest; Password=hrmtest;";
        public bool AddOperationLog(OperationLog log)
        {
            
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                string sql = "INSERT INTO OperationLog VALUES(@Id,@OperatorId,@ActionDate,@ActionDesc)";
                SqlParameter[] sqlParameters = new SqlParameter[]
                {
                    new SqlParameter("@Id", log.Id),
                    new SqlParameter("@OperatorId", log.OperatorId),
                    new SqlParameter("@ActionDate", log.ActionDate),
                    new SqlParameter("@ActionDesc",log.ActionDesc)
                };
                return SqlHelper.ExecuteNonQuery(sql,sqlParameters) > 0;
            }
        }
        public DataTable GetOperationLogList()
        {
            string sql = "SELECT OperationLog.Id AS Id, RealName AS 操作员, ActionDate AS 操作时间, ActionDesc AS 操作描述 FROM OperationLog, Operator WHERE OperationLog.OperatorId = Operator.Id";
            return SqlHelper.GetDataTable(sql);
        }
        public DataTable GetOperationLogList(int pageNo, int pageSizes)
        {
            string sql = "SELECT ROW_NUMBER() OVER (ORDER BY ActionDate DESC) AS No, OperationLog.Id AS Id, Operator.RealName AS 操作员, ActionDate AS 操作时间, ActionDesc AS 操作描述 from OperationLog, Operator WHERE OperationLog.OperatorId=Operator.Id ORDER BY No OFFSET @OffsetValue ROWS FETCH NEXT @PageSizes ROWS ONLY";
            return SqlHelper.GetDataTable(sql, new SqlParameter[]
            {
                new SqlParameter("@OffsetValue",(pageNo-1)*pageSizes),
                new SqlParameter("@PageSizes",pageSizes)
            });
        }
        public int GetTotalCount()
        {
            string sql = "SELECT COUNT(*) FROM OperationLog";
            return (int)SqlHelper.ExecuteScalar(sql);
        }
    }
}
